{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4db367a2-2b18-4558-99c3-ecebffb08d4a",
   "metadata": {},
   "source": [
    "#### Objective: Assign race, net and county columns to migration points - create final total population dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8fc5ec40-bdc1-43a2-b31d-3420d080490a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import cudf, cupy as cp\n",
    "import pandas as pd,numpy as np\n",
    "import random, pickle\n",
    "pd.set_option('display.max_colwidth', 100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "69446b40-455f-43b3-8e5d-b98817c55777",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_parquet('data/total_parts_combined.parquet').reset_index(drop=True)\n",
    "# df = cudf.from_pandas(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ec378db0-fa20-47f1-a5ac-64c4e5ef54af",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P20</th>\n",
       "      <th>eq_P10</th>\n",
       "      <th>block_diff</th>\n",
       "      <th>block_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>21</td>\n",
       "      <td>31.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>34</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>29</td>\n",
       "      <td>52.0</td>\n",
       "      <td>-23.0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>17</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20          COUNTY  P20  eq_P10  block_diff  block_net\n",
       "0  10010201001000  Autauga County   21    31.0       -10.0         -1\n",
       "1  10010201001001  Autauga County   34    30.0         4.0          1\n",
       "2  10010201001002  Autauga County   29    52.0       -23.0         -1\n",
       "3  10010201001003  Autauga County   17    13.0         4.0          1\n",
       "4  10010201001004  Autauga County    0     0.0         0.0          0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adf = pd.read_parquet('data/total_attr_gen_df.parquet')\n",
    "adf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "587e580d-0c12-45b3-b605-84ebdb151abb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P20</th>\n",
       "      <th>eq_P10</th>\n",
       "      <th>block_diff</th>\n",
       "      <th>block_net</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>21</td>\n",
       "      <td>31.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>34</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>29</td>\n",
       "      <td>52.0</td>\n",
       "      <td>-23.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>17</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20          COUNTY  P20  eq_P10  block_diff  block_net  points\n",
       "0  10010201001000  Autauga County   21    31.0       -10.0         -1    52.0\n",
       "1  10010201001001  Autauga County   34    30.0         4.0          1    34.0\n",
       "2  10010201001002  Autauga County   29    52.0       -23.0         -1    81.0\n",
       "3  10010201001003  Autauga County   17    13.0         4.0          1    17.0\n",
       "4  10010201001004  Autauga County    0     0.0         0.0          0     0.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adf['points'] = adf[['P20','eq_P10','block_net']].apply(lambda row: row[0] if row[-1] >=0 else row[0] + row[1],axis=1)\n",
    "adf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "72bad3af-8b44-4b0f-8b84-076cb2058ad5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "504475979.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adf.points.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4ace809-4ea7-4dd2-afae-be9dfbeb0864",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Compute races"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7a67aef5-4fd4-4834-93db-a34e31ae0dae",
   "metadata": {},
   "outputs": [],
   "source": [
    "full_2020_path='data/nhgis0007_csv/nhgis0007_ds248_2020_block.csv'\n",
    "races = cudf.read_csv(full_2020_path,usecols=['GEOCODE','U7B001','U7B003','U7B004','U7B005','U7B006','U7B007','U7B008','U7B009'],encoding='unicode_escape',dtype={'GEOCODE':'int64','U7B003':'int32','U7B004':'int32','U7B005':'int32','U7B006':'int32','U7B007':'int32','U7B008':'int32','U7B009':'int32'})\n",
    "races.rename(columns={'GEOCODE':'ID20'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "12848d22-d07b-43fc-a855-5d858cf6f434",
   "metadata": {},
   "outputs": [],
   "source": [
    "races['block_net'] = adf['block_net']\n",
    "races['P10'] = adf['eq_P10']\n",
    "races['points'] = adf['points']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5093de1c-f9e4-4dd3-8112-c0ec80f5b2de",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces = races.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "74230e20-a0eb-4dec-9ca3-662218f63e05",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P20</th>\n",
       "      <th>R1</th>\n",
       "      <th>R2</th>\n",
       "      <th>R3</th>\n",
       "      <th>R4</th>\n",
       "      <th>R5</th>\n",
       "      <th>R6</th>\n",
       "      <th>R7</th>\n",
       "      <th>block_net</th>\n",
       "      <th>P10</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>-1</td>\n",
       "      <td>31.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>34</td>\n",
       "      <td>18</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>29</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>52.0</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>17</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001004</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  P20  R1  R2  R3  R4  R5  R6  R7  block_net   P10  points\n",
       "0  10010201001000   21  12   4   0   0   0   0   5         -1  31.0    52.0\n",
       "1  10010201001001   34  18  11   0   2   0   1   2          1  30.0    34.0\n",
       "2  10010201001002   29  24   2   0   0   0   2   1         -1  52.0    81.0\n",
       "3  10010201001003   17  16   0   0   0   0   0   1          1  13.0    17.0\n",
       "4  10010201001004    0   0   0   0   0   0   0   0          0   0.0     0.0\n",
       "5  10010201001005    0   0   0   0   0   0   0   0         -1   8.0     8.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "craces.rename(columns={'U7B001':'P20','U7B003':'R1','U7B004':'R2','U7B005':'R3','U7B006':'R4','U7B007':'R5','U7B008':'R6','U7B009':'R7'},inplace=True)\n",
    "craces.head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a91d99c7-f39d-443a-adf5-1529dd6bea21",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces = craces[(craces.P20!=0) | (craces.P10!=0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "845fa664-de71-4749-83c3-3306ee3ab0b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "504475979.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "craces['P20'].sum() + craces[craces.block_net==-1]['P10'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "def7cb00-9cf5-455a-8c65-30b26d29cabb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "craces['P10'] = craces['P10'].astype('int32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a090d615-cb97-49f1-9302-851b9fda7ea2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "craces['R1'] = craces['R1'].apply(lambda x : [1]*x)\n",
    "craces['R2'] = craces['R2'].apply(lambda x : [2]*x)\n",
    "craces['R3'] = craces['R3'].apply(lambda x : [3]*x)\n",
    "craces['R4'] = craces['R4'].apply(lambda x : [4]*x)\n",
    "craces['R5'] = craces['R5'].apply(lambda x : [5]*x)\n",
    "craces['R6'] = craces['R6'].apply(lambda x : [6]*x)\n",
    "craces['R7'] = craces['R7'].apply(lambda x : [7]*x)\n",
    "craces['P10_list'] = craces[['P10','block_net']].apply(lambda row : [0]*row[0] if row[1] <0 else [],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1b2dfe2f-2594-4f7f-a4a0-bc938b9f8b78",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "craces['pop'] = craces['R1']+craces['R2']+craces['R3']+craces['R4']+craces['R5']+craces['R6']+craces['R7'] + craces['P10_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b85905ee-ebb3-44c5-a6ce-fd131d435e8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces = craces[['ID20','P20','P10','block_net','pop','points']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "91d30dc8-dccc-4455-a47d-a74506778531",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces = craces.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "85d0d077-a7e6-4d25-9048-67743160d311",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P20</th>\n",
       "      <th>P10</th>\n",
       "      <th>block_net</th>\n",
       "      <th>pop</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>185909</th>\n",
       "      <td>40131042032003</td>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]</td>\n",
       "      <td>173.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ID20  P20  P10  block_net  \\\n",
       "185909  40131042032003   84   89         -1   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                      pop  \\\n",
       "185909  [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]   \n",
       "\n",
       "        points  \n",
       "185909   173.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "craces.iloc[[185909]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b889cecc-33f5-454a-ba53-c6f5cbb79a38",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(craces,open('craces_list3.pkl','wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61aff00e-b8fe-43d2-8b78-ef649e9ee8c4",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Compute net"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "620ed428-103b-4714-8d9e-056ce85aa73f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P20</th>\n",
       "      <th>P10</th>\n",
       "      <th>block_net</th>\n",
       "      <th>pop</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>21</td>\n",
       "      <td>31</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>34</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 6, 7, 7]</td>\n",
       "      <td>34.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>29</td>\n",
       "      <td>52</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 6, 6, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 7]</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>-1</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265158</th>\n",
       "      <td>721537506022009</td>\n",
       "      <td>62</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265159</th>\n",
       "      <td>721537506022010</td>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265160</th>\n",
       "      <td>721537506022011</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265161</th>\n",
       "      <td>721537506022012</td>\n",
       "      <td>43</td>\n",
       "      <td>63</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265162</th>\n",
       "      <td>721537506022013</td>\n",
       "      <td>195</td>\n",
       "      <td>341</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, ...]</td>\n",
       "      <td>536.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6265163 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    ID20  P20  P10  block_net  \\\n",
       "0         10010201001000   21   31         -1   \n",
       "1         10010201001001   34   30          1   \n",
       "2         10010201001002   29   52         -1   \n",
       "3         10010201001003   17   13          1   \n",
       "4         10010201001005    0    8         -1   \n",
       "...                  ...  ...  ...        ...   \n",
       "6265158  721537506022009   62   24          1   \n",
       "6265159  721537506022010   21   23         -1   \n",
       "6265160  721537506022011   27    6          1   \n",
       "6265161  721537506022012   43   63         -1   \n",
       "6265162  721537506022013  195  341         -1   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                       pop  \\\n",
       "0                                                                                                                                                             [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "1                                                                                                                                                                                                                   [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 6, 7, 7]   \n",
       "2                                                                      [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 6, 6, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "3                                                                                                                                                                                                                                                                      [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 7]   \n",
       "4                                                                                                                                                                                                                                                                                                 [0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "...                                                                                                                                                                                                                                                                                                                    ...   \n",
       "6265158                                                                                                                         [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]   \n",
       "6265159                                                                                                                                                                               [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "6265160                                                                                                                                                                                                                                  [1, 1, 1, 1, 1, 1, 1, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]   \n",
       "6265161  [1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]   \n",
       "6265162  [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, ...]   \n",
       "\n",
       "         points  \n",
       "0          52.0  \n",
       "1          34.0  \n",
       "2          81.0  \n",
       "3          17.0  \n",
       "4           8.0  \n",
       "...         ...  \n",
       "6265158    62.0  \n",
       "6265159    44.0  \n",
       "6265160    27.0  \n",
       "6265161   106.0  \n",
       "6265162   536.0  \n",
       "\n",
       "[6265163 rows x 6 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "craces = pickle.load(open('craces_list3.pkl','rb'))\n",
    "craces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a7c7280a-2e79-4569-9ae1-c5b12d96b3b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # craces = races.to_pandas()\n",
    "# # craces.rename(columns={'U7B001':'P20','U7B003':'R1','U7B004':'R2','U7B005':'R3','U7B006':'R4','U7B007':'R5','U7B008':'R6','U7B009':'R7'},inplace=True)\n",
    "# craces = craces[['ID20','P20','P10','block_net','points']]\n",
    "# # del(races)\n",
    "# craces.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f640fe98-ee75-4ab6-9907-8a42ecc21232",
   "metadata": {},
   "outputs": [],
   "source": [
    "# craces = craces[(craces.P20!=0) | (craces.P10!=0) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cb7a86e9-65f6-40b2-ab85-12adca5dda78",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "504475979"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "craces.P20.sum() + craces[(craces.block_net==-1)].P10.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "238ca5f9-95ef-4377-a26c-208c01cda8e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# craces[['P20','P10','block_net','points']] = craces[['P20','P10','block_net','points']].astype('int32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8bb4eb5e-2a31-4a6e-ab4f-82611682b534",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces['net_list'] = craces[['P20','P10','block_net']].apply(lambda row: [1]*(row[0]-row[1]) + [0]*row[1]  if row[2] >=0 else [0]*row[0] + [-1]*(row[1]-row[0]) + [-99]*row[0],axis=1) # in -> p20-p10; stationary -> p10; | stationary-> p20   out -> p10-p20 extra -> p20  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "12f2ab2c-0fbb-40ba-8038-bcd0fe45d94b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P20</th>\n",
       "      <th>P10</th>\n",
       "      <th>block_net</th>\n",
       "      <th>pop</th>\n",
       "      <th>points</th>\n",
       "      <th>net_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>21</td>\n",
       "      <td>31</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>52.0</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>34</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 6, 7, 7]</td>\n",
       "      <td>34.0</td>\n",
       "      <td>[1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>29</td>\n",
       "      <td>52</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 6, 6, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>81.0</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 7]</td>\n",
       "      <td>17.0</td>\n",
       "      <td>[1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>-1</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>8.0</td>\n",
       "      <td>[-1, -1, -1, -1, -1, -1, -1, -1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265158</th>\n",
       "      <td>721537506022009</td>\n",
       "      <td>62</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]</td>\n",
       "      <td>62.0</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265159</th>\n",
       "      <td>721537506022010</td>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]</td>\n",
       "      <td>44.0</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265160</th>\n",
       "      <td>721537506022011</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]</td>\n",
       "      <td>27.0</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265161</th>\n",
       "      <td>721537506022012</td>\n",
       "      <td>43</td>\n",
       "      <td>63</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]</td>\n",
       "      <td>106.0</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, ...]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6265162</th>\n",
       "      <td>721537506022013</td>\n",
       "      <td>195</td>\n",
       "      <td>341</td>\n",
       "      <td>-1</td>\n",
       "      <td>[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, ...]</td>\n",
       "      <td>536.0</td>\n",
       "      <td>[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6265163 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    ID20  P20  P10  block_net  \\\n",
       "0         10010201001000   21   31         -1   \n",
       "1         10010201001001   34   30          1   \n",
       "2         10010201001002   29   52         -1   \n",
       "3         10010201001003   17   13          1   \n",
       "4         10010201001005    0    8         -1   \n",
       "...                  ...  ...  ...        ...   \n",
       "6265158  721537506022009   62   24          1   \n",
       "6265159  721537506022010   21   23         -1   \n",
       "6265160  721537506022011   27    6          1   \n",
       "6265161  721537506022012   43   63         -1   \n",
       "6265162  721537506022013  195  341         -1   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                       pop  \\\n",
       "0                                                                                                                                                             [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "1                                                                                                                                                                                                                   [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 6, 7, 7]   \n",
       "2                                                                      [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 6, 6, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "3                                                                                                                                                                                                                                                                      [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 7]   \n",
       "4                                                                                                                                                                                                                                                                                                 [0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "...                                                                                                                                                                                                                                                                                                                    ...   \n",
       "6265158                                                                                                                         [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]   \n",
       "6265159                                                                                                                                                                               [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]   \n",
       "6265160                                                                                                                                                                                                                                  [1, 1, 1, 1, 1, 1, 1, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7]   \n",
       "6265161  [1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]   \n",
       "6265162  [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, ...]   \n",
       "\n",
       "         points  \\\n",
       "0          52.0   \n",
       "1          34.0   \n",
       "2          81.0   \n",
       "3          17.0   \n",
       "4           8.0   \n",
       "...         ...   \n",
       "6265158    62.0   \n",
       "6265159    44.0   \n",
       "6265160    27.0   \n",
       "6265161   106.0   \n",
       "6265162   536.0   \n",
       "\n",
       "                                                                                                                                                                                                                                                                                                                                                                                                                net_list  \n",
       "0                                                                                                                                                                                                       [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99]  \n",
       "1                                                                                                                                                                                                                                                                                                                 [1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]  \n",
       "2                                                                                   [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99]  \n",
       "3                                                                                                                                                                                                                                                                                                                                                                    [1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]  \n",
       "4                                                                                                                                                                                                                                                                                                                                                                                       [-1, -1, -1, -1, -1, -1, -1, -1]  \n",
       "...                                                                                                                                                                                                                                                                                                                                                                                                                  ...  \n",
       "6265158                                                                                                                                                                                                                       [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]  \n",
       "6265159                                                                                                                                                                                                                                 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99]  \n",
       "6265160                                                                                                                                                                                                                                                                                                                                [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0]  \n",
       "6265161  [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, -99, ...]  \n",
       "6265162                                                                                                [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]  \n",
       "\n",
       "[6265163 rows x 7 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "craces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "88b24998-a917-4909-9aed-a7181e9e910f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pickle.dump(craces,open('race_and_net_list.pkl','wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45fff38a-cd7f-4c99-a4ac-995b13b79600",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces = pickle.load(open('race_and_net_list.pkl','rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f5cca43a-abea-4a58-b990-c255f64e09db",
   "metadata": {},
   "outputs": [],
   "source": [
    "# graces = cudf.from_pandas(craces['pop'])\n",
    "# gnet = cudf.from_pandas(craces['net_list'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "62741f25-38ff-4f74-99a0-a937b22e794d",
   "metadata": {},
   "outputs": [],
   "source": [
    "edf = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dc17b917-5a63-41ae-a0c6-8c594f7c70b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# edf['races'] = graces.explode('pop').reset_index(drop=True)['pop']\n",
    "edf['races'] = craces['pop'].explode().reset_index(drop=True)\n",
    "edf['net_list'] = craces['net_list'].explode().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "63b7ead4-74bf-43fc-9134-0cdca844746c",
   "metadata": {},
   "outputs": [],
   "source": [
    "edf.rename(columns={'net_list':'net'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "962445ab-afe8-470f-a7c2-70c96a2fb55c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pickle.dump(edf,open('race_and_net_exploded.pkl','wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "aced8e7b-5fde-4497-8489-3e78e6bffbd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "edf = pickle.load(open('race_and_net_exploded.pkl','rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5a8e2790-edcd-4aa2-86fd-09877cfb6bd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_ser = pickle.load(open('county_list.pkl','rb'))\n",
    "county_ser = county_ser.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "16441b10-596e-43c2-a0a4-2dce1be04d89",
   "metadata": {},
   "outputs": [],
   "source": [
    "edf['county'] = county_ser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ae015204-6c27-41bb-bc54-d0da84175434",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['race','net','county']] = edf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3306b1b9-5e2d-4a6b-b2b8-e870ef163439",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.to_parquet('data/total_population_dataset.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "eb076245-1e05-4ce5-a898-490418e38e7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_parquet('data/total_population_dataset.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "01d87038-a78a-4527-8c52-13c2e62f22f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.net!=-99]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2b2df50e-0c9f-428b-a0fe-29f62e8bc2d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_parquet('data/total_population_dataset.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e8e708a2-d1ec-4d33-b4a5-bc4329a10bde",
   "metadata": {},
   "outputs": [],
   "source": [
    "small = df.sample(1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8179ac9b-a0c1-495b-9ea8-5b59ed122af4",
   "metadata": {},
   "outputs": [],
   "source": [
    "small.to_parquet('data/total_population_dataset_sm.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "834b8cf4-d203-40f1-bcd9-672d5fe1c0d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "504475979"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(net_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9c41533f-97a7-4752-9bc1-707d273e99b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(net_list,open('data/net_exploded3.pkl','wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5402141a-da7d-4150-a5b2-da1d8ae76685",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Get stored computed races"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "cf27b4f6-5f17-4903-8f87-9450bae42953",
   "metadata": {},
   "outputs": [],
   "source": [
    "craces = pickle.load(open('craces_list3.pkl','rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cef3ae15-ba3c-4046-8983-e9c9f4352d8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "graces = cudf.from_pandas(craces[['ID20','pop']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "049d79b2-9bf1-49b5-9b51-780535eaaf66",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp =graces.explode('pop')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "0ef12926-bf72-4468-a56c-0879d9c60f31",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = temp.reset_index(drop=True)['pop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "dd9772e0-e5a5-4fce-b335-ec3d04186181",
   "metadata": {},
   "outputs": [],
   "source": [
    "ctemp = temp.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "16b66085-b87f-428e-a1f5-11c1ac3bd932",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: pop, dtype: int64)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ctemp[ctemp==-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a208570a-833c-44d1-a4c4-a4c2b08a4985",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ctemp.reset_index(drop=True).to_parquet('data/total_races.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "29261263-b41f-4426-888e-b8d513b2e7e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(ctemp,open('data/races_exploded3.pkl','wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5960cbb-cd82-4a80-8b52-b8bb729ae905",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Concat races and stored counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "21e39c11-4626-4a03-afd3-adcda8388a1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "races = pickle.load(open('data/races_exploded3.pkl','rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b2895c8b-274b-4984-b66a-3b2bb0a22277",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['races'] = races"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ec3cd19a-4272-4bb4-8da1-552764420c8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop('ID20',axis=1) # change if you need id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a4e4b2dc-fc8d-46f9-84b5-49075c6aeb76",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_ser = pickle.load(open('county_list.pkl','rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1fcf6af1-a87f-4b58-9252-417af12475ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>races</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-86.478941</td>\n",
       "      <td>32.470190</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-86.479300</td>\n",
       "      <td>32.469311</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-86.481722</td>\n",
       "      <td>32.469914</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-86.482149</td>\n",
       "      <td>32.471196</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-86.482242</td>\n",
       "      <td>32.471287</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           x          y  races\n",
       "0 -86.478941  32.470190      1\n",
       "1 -86.479300  32.469311      1\n",
       "2 -86.481722  32.469914      1\n",
       "3 -86.482149  32.471196      1\n",
       "4 -86.482242  32.471287      1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.reset_index(drop=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3ee44af5-7bcf-4421-ab01-cca9b519b38d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['county'] = county_ser.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9476adbf-c384-485e-81b4-1bb8e3206c77",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_parquet('data/population_county_races3.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "029b48ae-8e35-41f4-a9ce-bcb7b4e6ae60",
   "metadata": {},
   "source": [
    "### Concat net"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d545a390-436a-4bdf-92a2-67777c0e0b51",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr = pd.read_parquet('data/population_county_races3.parquet')\n",
    "net = pickle.load(open('data/net_exploded3.pkl','rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "7cb60420-c9e4-49e5-9e47-9cc34869151f",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr = attr.reset_index(drop=True)\n",
    "net = net.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e2e1402f-0aa2-4323-8173-f356e2876fd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "attr['net'] = net.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8ade304e-ddbb-427e-a238-998f8202718c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attr = pd.read_parquet('data/total_population_dataset3.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1e19fb42-f205-4f95-8a7e-e53223538f73",
   "metadata": {},
   "outputs": [],
   "source": [
    "# attr.to_parquet('data/total_population_dataset3.parquet')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
